package com.szholly.pro.watergas_server.ps.android.common;

import com.szholly.data.general.*;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.utils.util.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

/**
 * 临时安卓工具类，非作者勿用，具体明细请咨询林瑞强
 *
 * 
 * 2017/3/23 <br/>
 *
 * @author Joey Huang QQ:273179370）
 */
public class SqlUtils extends DataUtil {
	private static FieldInterceptor lengthInterceptor = new FieldInterceptor.FieldLengthInterceptor();
	private static FieldInterceptor nullInterceptor = new FieldInterceptor.FieldLengthInterceptor();
	private static FieldInterceptor[] DEFAULT_INTERCEPTOR = { lengthInterceptor, nullInterceptor };

	public static void update(TableRowMap rowBase, RowBase whereParms, FieldInterceptor... interceptors) throws Exception {
		ValidUtils.notEmpty(whereParms, "条件语句不能为空");
		ValidUtils.notNull(rowBase, "数据不能为空");
		String tableName = rowBase.getTableName();
		ValidUtils.hasText(tableName, "表名不能为空");
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		ITable pTable = tableFactory.openTable(tableName);
		ValidUtils.notNull(pTable, "表实体不存在：" + tableName);
		Field[] fields = pTable.getFields();
		ValidUtils.notEmpty(fields, "表实体字段为空");
		if (interceptors != null && interceptors.length > 0) {// 添加默认的校验
			List<FieldInterceptor> interceptorList = new ArrayList<FieldInterceptor>();
			interceptorList.addAll(Arrays.asList(DEFAULT_INTERCEPTOR));
			interceptorList.addAll(Arrays.asList(interceptors));
			FieldInterceptor.validateField(fields, rowBase, interceptorList);
		} else {// 没有特殊的校验要求，则使用默认的校验
			FieldInterceptor.validateField(fields, rowBase, DEFAULT_INTERCEPTOR);
		}
		try {
			String sql = createUpdateSql(pTable, rowBase, whereParms);
			rowBase.putAll(whereParms);
			GlobalParameter[] parmsArray = getParameter(pTable, rowBase, true);
			beginTransaction();
			pWorkspace.executeSql(sql, parmsArray);
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		}
	}

	public static void update(TableRowMap rowBase, String[] primaryKeyArray, FieldInterceptor... interceptors) throws Exception {
		ValidUtils.isTrue(primaryKeyArray != null && primaryKeyArray.length > 0, "主键不能为空");
		ValidUtils.notNull(rowBase, "数据不能为空");
		String tableName = rowBase.getTableName();
		ValidUtils.hasText(tableName, "表名不能为空");

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		ITable pTable = tableFactory.openTable(tableName);
		ValidUtils.notNull(pTable, "表实体不存在：" + tableName);

		Field[] fields = pTable.getFields();
		ValidUtils.notEmpty(fields, "表实体字段为空");

		if (interceptors != null && interceptors.length > 0) {// 添加默认的校验
			List<FieldInterceptor> interceptorList = new ArrayList<FieldInterceptor>();
			interceptorList.addAll(Arrays.asList(DEFAULT_INTERCEPTOR));
			interceptorList.addAll(Arrays.asList(interceptors));
			FieldInterceptor.validateField(fields, rowBase, interceptorList);
		} else {// 没有特殊的校验要求，则使用默认的校验
			FieldInterceptor.validateField(fields, rowBase, DEFAULT_INTERCEPTOR);
		}
		String sql = createUpdateSql(pTable, rowBase, primaryKeyArray);
		GlobalParameter[] parmsArray = getParameter(pTable, rowBase, true);
		try {			
			beginTransaction();
			pWorkspace.executeSql(sql, parmsArray);
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		}
	}

	public static void prepareUpdate(TableRowMap rowBase, RowBase whereParms, FieldInterceptor... interceptors) throws Exception {
		ValidUtils.notEmpty(whereParms, "条件语句不能为空");
		ValidUtils.notNull(rowBase, "数据不能为空");
		String tableName = rowBase.getTableName();
		ValidUtils.hasText(tableName, "表名不能为空");
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		ITable pTable = tableFactory.openTable(tableName);
		ValidUtils.notNull(pTable, "表实体不存在：" + tableName);
		Field[] fields = pTable.getFields();
		ValidUtils.notEmpty(fields, "表实体字段为空");
		if (interceptors != null && interceptors.length > 0) {// 添加默认的校验
			List<FieldInterceptor> interceptorList = new ArrayList<FieldInterceptor>();
			interceptorList.addAll(Arrays.asList(DEFAULT_INTERCEPTOR));
			interceptorList.addAll(Arrays.asList(interceptors));
			FieldInterceptor.validateField(fields, rowBase, interceptorList);
		} else {// 没有特殊的校验要求，则使用默认的校验
			FieldInterceptor.validateField(fields, rowBase, DEFAULT_INTERCEPTOR);
		}
		String sql = createUpdateSql(pTable, rowBase, whereParms);
		rowBase.putAll(whereParms);
		GlobalParameter[] parmsArray = getParameter(pTable, rowBase, true);
		pWorkspace.executeSql(sql, parmsArray);
	}

	/**
	 * 批量插入数据，当其中一条报错，其它全部不插入
	 * 
	 * @param insertList
	 * @return 返回 GUID 集合
	 * @throws Exception
	 */
	public static List<String> insert(List<TableRowMap> insertList) throws Exception {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		try {
			List<String> guidList = new ArrayList<>();
			DataUtil.beginTransaction();
			for (TableRowMap bean : insertList) {
				String tableName = bean.getTableName();
				ValidUtils.notNull(tableName, "表名不能为空");
				ITable pTable = tableFactory.openTable(tableName);
				prepareInsert(pWorkspace, pTable, tableName, bean, bean.getFieldInterceptor());
			}
			DataUtil.commitTransaction();
			return guidList;
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		}
	}

	/**
	 * 批量插入数据,未开启事务
	 * 
	 * @param insertList
	 * @return 返回 GUID 集合
	 * @throws Exception
	 */
	public static List<String> prepareInsert(List<TableRowMap> insertList) throws Exception {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		List<String> guidList = new ArrayList<>();
		for (TableRowMap bean : insertList) {
			String tableName = bean.getTableName();
			ValidUtils.notNull(tableName, "表名不能为空");
			ITable pTable = tableFactory.openTable(tableName);
			prepareInsert(pWorkspace, pTable, tableName, bean, bean.getFieldInterceptor());
		}
		return guidList;
	}

	/**
	 * 插入一条数据，
	 * 
	 * @param rowBase
	 *            一条数据(不能为空)
	 * @throws Exception
	 */
	public static void insert(TableRowMap rowBase) throws Exception {
		ValidUtils.notNull(rowBase, "数据不能为空");
		insert(rowBase.getTableName(), rowBase, rowBase.getFieldInterceptor());
	}

	/**
	 * 插入一条数据，
	 * 
	 * @param tableName
	 *            表名(不能为空)
	 * @param rowBase
	 *            一条数据(不能为空)
	 * @param interceptors
	 *            数据校验器(可以为空)
	 * @throws Exception
	 */
	public static void insert(String tableName, HashMap<String, Object> rowBase, FieldInterceptor... interceptors) throws Exception {
		ValidUtils.notNull(tableName, "表名不能为空");
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		ITable pTable = tableFactory.openTable(tableName);
		try {
			DataUtil.beginTransaction();
			prepareInsert(pWorkspace, pTable, tableName, rowBase, interceptors);
			DataUtil.commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			throw e;
		}
	}

	private static void prepareInsert(IWorkspace pWorkspace, ITable pTable, String tableName, HashMap<String, Object> rowBase,
			FieldInterceptor... interceptors) throws Exception {
		ValidUtils.notNull(pTable, "表实体不存在：" + tableName);
		ValidUtils.notNull(pWorkspace, "表工作空间不存在");
		Field[] fields = pTable.getFields();
		ValidUtils.notEmpty(fields, "表示体字段为空:" + tableName);

		makeSureContainGUID(rowBase);

		if (interceptors != null && interceptors.length > 0) {// 添加默认的校验
			List<FieldInterceptor> interceptorList = new ArrayList<FieldInterceptor>();
			interceptorList.add(lengthInterceptor);
			interceptorList.addAll(Arrays.asList(interceptors));
			FieldInterceptor.validateField(fields, rowBase, interceptorList);
		} else {// 没有特殊的校验要求，则使用默认的校验
			FieldInterceptor.validateField(fields, rowBase, lengthInterceptor);
		}
		// CREATE_TIME UPDATE_TIME会生成默认值
		String sql = DataUtil.getInsertSql(pTable, rowBase);
		GlobalParameter[] parmsArray = getParameter(pTable, rowBase, false);
		pWorkspace.executeSql(sql, parmsArray);
	}

	private static String makeSureContainGUID(HashMap<String, Object> bean) throws Exception {
		String guid = (String) bean.get("GUID");
		if (StringUtils.IsNullOrSpace(guid)) {
			guid = UUID.randomUUID().toString();
		}
		bean.put("GUID", guid);
		return guid;
	}

	/**
	 * @param params
	 *            {"aa","bb","cc"}
	 * @return 'aa','bb','cc'
	 */
	public static String getMultiParameter(String[] params) {
		if (params == null || params.length == 0) {
			return "";
		}
		String result = "";
		for (int i = 0; i < params.length; i++) {
			result += "'" + params[i] + "'" + ",";
		}
		if (result.length() > 0) {
			return result.substring(0, result.length() - 1);
		} else {
			return result;
		}
	}

	/**
	 * 获取行数
	 */
	public static int getRowsCount(String sql) throws Exception {
		return getRowsCount(sql, null);
	}

	/**
	 * 获取行数
	 */
	public static int getRowsCount(String sql, List<GlobalParameter> parms) throws Exception {
		String totalSql = "SELECT COUNT(*) count from (" + sql + ")";
		List<RowBase> rows = DataUtil.getSelectRows(totalSql, parms);
		if (rows != null && rows.size() > 0) {
			RowBase rowBase = rows.get(0);
			return rowBase.getSafeInt("COUNT");
		} else {
			return 0;
		}
	}

	private static String createUpdateSql(ITable pTable, Map<String, Object> parms, String[] primaryKeyArray) throws Exception {
		String sql = "";
		String whereString = "";
		Field[] fields = pTable.getFields();

		Set<String> primaryKeySet = new HashSet<>();
		for (String primaryKey : primaryKeyArray) {
			primaryKeySet.add(primaryKey);
		}

		for (Field item : fields) {
			String fieldName = item.getFieldName();
			if (parms.containsKey(item.getFieldName()) && !isEmpty(item, parms.get(fieldName)) || fieldName.equals("UPDATE_TIME")) {
				if (primaryKeySet.contains(fieldName)) {
					if (parms.get(fieldName) != null) {
						whereString += "and " + fieldName + " = @" + fieldName;
					}
				} else {
					sql += "[" + fieldName + "]=@" + fieldName + ",";
				}
			}
		}

		if (whereString.trim().length() == 0) {
			throw new Exception("查询条件不能为空");
		}
		if (sql.length() > 0) {
			sql = sql.substring(0, sql.length() - 1);
			sql = "update " + pTable.getTableName() + " set " + sql + " where 1 = 1 " + whereString;
		}
		return sql;
	}

	private static String createUpdateSql(ITable pTable, Map<String, Object> parms, RowBase whereParms) throws Exception {
		String sql = "";
		String whereString = "";
		Field[] fields = pTable.getFields();

		for (Field field : fields) {
			String keyName = field.getFieldName();
			Object object = whereParms.get(keyName);
			if (object != null) {
				whereString += "and " + keyName + " = @" + keyName;
			}
		}
		whereString = whereString.replaceFirst("and", "");

		for (Field item : fields) {
			String fieldName = item.getFieldName();

			if (parms.containsKey(item.getFieldName()) && !isEmpty(item, parms.get(fieldName)) || fieldName.equals("UPDATE_TIME")) {
				sql += "[" + fieldName + "]=@" + fieldName + ",";
			}
		}
		if (whereString.trim().length() == 0) {
			throw new Exception("查询条件不能为空");
		}
		if (sql.length() > 0) {
			sql = sql.substring(0, sql.length() - 1);
			sql = "update " + pTable.getTableName() + " set " + sql + " where " + whereString;
		}
		return sql;
	}
}
